﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Entity;

namespace DAO
{
    public class QuanLySanPhamDAO
    {
        public static DataTable LayThongTinSanPham()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                String cmdText = "SELECT a.MaSP,TenSP,MoTaChiTiet,DonViTinh,TenLoaiSP, slnhap, slxuat " +
                                "FROM SAN_PHAM a, LOAI_SAN_PHAM b,	(Select b.MaSP, Sum(SoLuong) as slxuat " +
									                                "From SAN_PHAM b Left outer join CHI_TIET_XUAT_HANG a on a.MaSP = b.MaSP " +
									                                "Group by b.MaSP) c, " +
									                                "(Select b.MaSP, Sum(SoLuong) as slnhap " +
									                                "From SAN_PHAM b Left outer join CHI_TIET_NHAP_HANG a on a.MaSP = b.MaSP " +
									                                "Group by b.MaSP) d " +
                                "WHERE a.MaLoaiSP = b.MaLoaiSP and c.MaSP = a.MaSP and d.MaSP = a.MaSP";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static void XoaSanPham(String masp)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@masp", masp);
                String cmdText = "DELETE SAN_PHAM WHERE MaSP = @masp";
                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static DataTable LaySanPham(String masp)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@masp", masp);
                String cmdText = "SELECT TenSP,MoTaChiTiet,DonViTinh,TenLoaiSP FROM SAN_PHAM a, LOAI_SAN_PHAM b " +
                                "WHERE a.MaLoaiSP = b.MaLoaiSP and MaSP = @masp";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText,param).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static DataTable LayTenLoaiSP()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                String cmdText = "SELECT TenLoaiSP FROM LOAI_SAN_PHAM";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static String LayMaLoaiSP(String tenloaisp)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@tenloaisp", tenloaisp);
                String cmdText = "SELECT MaLoaiSP FROM LOAI_SAN_PHAM WHERE TenLoaiSP = @tenloaisp";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText, param).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs.Rows[0][0].ToString();
        }

        public static void CapNhatSanPham(SanPham sp, String masp)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[5];
                param[0] = new SqlParameter("@masp", masp);
                param[1] = new SqlParameter("@tensp", sp.tensanpham);
                param[2] = new SqlParameter("@mota", sp.motachitiet);
                param[3] = new SqlParameter("@donvitinh", sp.donvitinh);
                param[4] = new SqlParameter("@maloaisp", sp.maloaisp);
                SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_SuaSanPham", param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static void ThemSP(SanPham sp)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[4];
                param[0] = new SqlParameter("@tensp", sp.tensanpham);
                param[1] = new SqlParameter("@mota", sp.motachitiet);
                param[2] = new SqlParameter("@donvitinh", sp.donvitinh);
                param[3] = new SqlParameter("@maloaisp", sp.maloaisp);
                SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_ThemSanPham", param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }
    }
}
